{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T11:11:46.446678Z",
     "iopub.status.busy": "2021-05-28T11:11:46.445677Z",
     "iopub.status.idle": "2021-05-28T11:11:48.162809Z",
     "shell.execute_reply": "2021-05-28T11:11:48.160805Z",
     "shell.execute_reply.started": "2021-05-28T11:11:46.446678Z"
    }
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "np.set_printoptions(precision=2)\n",
    "pd.set_option('display.float_format', lambda x: '%.2f' % x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T11:11:48.168806Z",
     "iopub.status.busy": "2021-05-28T11:11:48.167807Z",
     "iopub.status.idle": "2021-05-28T11:11:48.178808Z",
     "shell.execute_reply": "2021-05-28T11:11:48.176806Z",
     "shell.execute_reply.started": "2021-05-28T11:11:48.168806Z"
    }
   },
   "outputs": [],
   "source": [
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T11:11:48.186809Z",
     "iopub.status.busy": "2021-05-28T11:11:48.185808Z",
     "iopub.status.idle": "2021-05-28T11:11:48.209807Z",
     "shell.execute_reply": "2021-05-28T11:11:48.207809Z",
     "shell.execute_reply.started": "2021-05-28T11:11:48.186809Z"
    }
   },
   "outputs": [],
   "source": [
    "cwd = os.getcwd()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T11:11:48.214812Z",
     "iopub.status.busy": "2021-05-28T11:11:48.213810Z",
     "iopub.status.idle": "2021-05-28T11:11:48.225813Z",
     "shell.execute_reply": "2021-05-28T11:11:48.223810Z",
     "shell.execute_reply.started": "2021-05-28T11:11:48.214812Z"
    }
   },
   "outputs": [],
   "source": [
    "data_dir = os.path.join(cwd,\"数据\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T11:11:49.092880Z",
     "iopub.status.busy": "2021-05-28T11:11:49.091880Z",
     "iopub.status.idle": "2021-05-28T11:11:49.117902Z",
     "shell.execute_reply": "2021-05-28T11:11:49.115901Z",
     "shell.execute_reply.started": "2021-05-28T11:11:49.092880Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['.ipynb_checkpoints',\n",
       " 'AA.xlsx',\n",
       " 'data_fam_fleet.csv',\n",
       " 'data_fam_market_share.csv',\n",
       " 'data_fam_products.csv',\n",
       " 'data_fam_schedule.csv']"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "os.listdir(data_dir)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T11:11:53.691310Z",
     "iopub.status.busy": "2021-05-28T11:11:53.690305Z",
     "iopub.status.idle": "2021-05-28T11:11:53.698306Z",
     "shell.execute_reply": "2021-05-28T11:11:53.697304Z",
     "shell.execute_reply.started": "2021-05-28T11:11:53.691310Z"
    }
   },
   "outputs": [],
   "source": [
    "data_files = [file for file in os.listdir(data_dir) if os.path.splitext(file)[1] in ['.xlsx','.csv']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T11:11:54.563373Z",
     "iopub.status.busy": "2021-05-28T11:11:54.562372Z",
     "iopub.status.idle": "2021-05-28T11:11:54.584373Z",
     "shell.execute_reply": "2021-05-28T11:11:54.583373Z",
     "shell.execute_reply.started": "2021-05-28T11:11:54.563373Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['AA.xlsx',\n",
       " 'data_fam_fleet.csv',\n",
       " 'data_fam_market_share.csv',\n",
       " 'data_fam_products.csv',\n",
       " 'data_fam_schedule.csv']"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T06:12:53.845857Z",
     "iopub.status.busy": "2021-05-28T06:12:53.845857Z",
     "iopub.status.idle": "2021-05-28T06:13:07.834729Z",
     "shell.execute_reply": "2021-05-28T06:13:07.834729Z",
     "shell.execute_reply.started": "2021-05-28T06:12:53.845857Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Flight1</th>\n",
       "      <th>Flight2</th>\n",
       "      <th>Flight3</th>\n",
       "      <th>Class</th>\n",
       "      <th>Fare</th>\n",
       "      <th>RD0</th>\n",
       "      <th>RD1</th>\n",
       "      <th>RD2</th>\n",
       "      <th>...</th>\n",
       "      <th>RD89</th>\n",
       "      <th>RD99</th>\n",
       "      <th>RD119</th>\n",
       "      <th>RD139</th>\n",
       "      <th>RD159</th>\n",
       "      <th>RD179</th>\n",
       "      <th>RD209</th>\n",
       "      <th>RD239</th>\n",
       "      <th>RD269</th>\n",
       "      <th>RD330</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>B</td>\n",
       "      <td>230.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>G</td>\n",
       "      <td>100.00</td>\n",
       "      <td>0.67</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>H</td>\n",
       "      <td>105.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>K</td>\n",
       "      <td>94.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>L</td>\n",
       "      <td>145.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.67</td>\n",
       "      <td>1.33</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 38 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Origin Destination       Flight1 Flight2 Flight3 Class   Fare  RD0  RD1  \\\n",
       "0    BER         BOD  AA0040BERBOD       .       .     B 230.00 0.00 0.00   \n",
       "1    BER         BOD  AA0040BERBOD       .       .     G 100.00 0.67 0.00   \n",
       "2    BER         BOD  AA0040BERBOD       .       .     H 105.00 0.00 0.00   \n",
       "3    BER         BOD  AA0040BERBOD       .       .     K  94.00 0.00 0.00   \n",
       "4    BER         BOD  AA0040BERBOD       .       .     L 145.00 0.00 0.00   \n",
       "\n",
       "   RD2  ...  RD89  RD99  RD119  RD139  RD159  RD179  RD209  RD239  RD269  \\\n",
       "0 0.00  ...  0.00  0.00   0.00   0.00   0.00   0.00   0.00   0.00   0.00   \n",
       "1 0.00  ...  0.00  0.00   0.00   0.00   0.00   0.00   0.00   0.00   0.00   \n",
       "2 0.00  ...  0.00  0.00   0.00   0.00   0.00   0.00   0.00   0.00   0.00   \n",
       "3 0.00  ...  0.00  0.00   0.00   0.00   0.00   0.00   0.00   0.00   0.00   \n",
       "4 0.00  ...  0.00  0.67   1.33   0.00   0.00   0.00   0.00   0.00   0.00   \n",
       "\n",
       "   RD330  \n",
       "0   0.00  \n",
       "1   0.00  \n",
       "2   0.00  \n",
       "3   0.00  \n",
       "4   0.00  \n",
       "\n",
       "[5 rows x 38 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "old_df = pd.read_excel(os.path.join(data_dir,data_files[0]))\n",
    "old_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T11:11:58.599952Z",
     "iopub.status.busy": "2021-05-28T11:11:58.598951Z",
     "iopub.status.idle": "2021-05-28T11:11:58.826967Z",
     "shell.execute_reply": "2021-05-28T11:11:58.824983Z",
     "shell.execute_reply.started": "2021-05-28T11:11:58.599952Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Flight1</th>\n",
       "      <th>Flight2</th>\n",
       "      <th>Flight3</th>\n",
       "      <th>Class</th>\n",
       "      <th>Fare</th>\n",
       "      <th>RD0</th>\n",
       "      <th>RD1</th>\n",
       "      <th>RD2</th>\n",
       "      <th>...</th>\n",
       "      <th>RD89</th>\n",
       "      <th>RD99</th>\n",
       "      <th>RD119</th>\n",
       "      <th>RD139</th>\n",
       "      <th>RD159</th>\n",
       "      <th>RD179</th>\n",
       "      <th>RD209</th>\n",
       "      <th>RD239</th>\n",
       "      <th>RD269</th>\n",
       "      <th>RD330</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>B</td>\n",
       "      <td>230.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>G</td>\n",
       "      <td>100.00</td>\n",
       "      <td>0.67</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>H</td>\n",
       "      <td>105.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>K</td>\n",
       "      <td>94.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>L</td>\n",
       "      <td>145.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.67</td>\n",
       "      <td>1.33</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 38 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Origin Destination       Flight1 Flight2 Flight3 Class   Fare  RD0  RD1  \\\n",
       "0    BER         BOD  AA0040BERBOD       .       .     B 230.00 0.00 0.00   \n",
       "1    BER         BOD  AA0040BERBOD       .       .     G 100.00 0.67 0.00   \n",
       "2    BER         BOD  AA0040BERBOD       .       .     H 105.00 0.00 0.00   \n",
       "3    BER         BOD  AA0040BERBOD       .       .     K  94.00 0.00 0.00   \n",
       "4    BER         BOD  AA0040BERBOD       .       .     L 145.00 0.00 0.00   \n",
       "\n",
       "   RD2  ...  RD89  RD99  RD119  RD139  RD159  RD179  RD209  RD239  RD269  \\\n",
       "0 0.00  ...  0.00  0.00   0.00   0.00   0.00   0.00   0.00   0.00   0.00   \n",
       "1 0.00  ...  0.00  0.00   0.00   0.00   0.00   0.00   0.00   0.00   0.00   \n",
       "2 0.00  ...  0.00  0.00   0.00   0.00   0.00   0.00   0.00   0.00   0.00   \n",
       "3 0.00  ...  0.00  0.00   0.00   0.00   0.00   0.00   0.00   0.00   0.00   \n",
       "4 0.00  ...  0.00  0.67   1.33   0.00   0.00   0.00   0.00   0.00   0.00   \n",
       "\n",
       "   RD330  \n",
       "0   0.00  \n",
       "1   0.00  \n",
       "2   0.00  \n",
       "3   0.00  \n",
       "4   0.00  \n",
       "\n",
       "[5 rows x 38 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df = pd.read_csv(os.path.join(data_dir,data_files[3]))\n",
    "new_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-27T13:04:13.350310Z",
     "iopub.status.busy": "2021-05-27T13:04:13.350310Z",
     "iopub.status.idle": "2021-05-27T13:04:13.368731Z",
     "shell.execute_reply": "2021-05-27T13:04:13.368731Z",
     "shell.execute_reply.started": "2021-05-27T13:04:13.350310Z"
    },
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "def calc(df):\n",
    "    df1 = df.drop_duplicates()\n",
    "    df1[\"TRD\"] = df1.iloc[:,7:].sum(axis=1)\n",
    "    df1.drop(axis=1,columns=df.columns[7:],inplace=True)\n",
    "    return df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-27T13:04:15.076419Z",
     "iopub.status.busy": "2021-05-27T13:04:15.076419Z",
     "iopub.status.idle": "2021-05-27T13:04:15.359525Z",
     "shell.execute_reply": "2021-05-27T13:04:15.359525Z",
     "shell.execute_reply.started": "2021-05-27T13:04:15.076419Z"
    },
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "old_df1 = calc(old_df)\n",
    "new_df1 = calc(new_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": true,
    "execution": {
     "iopub.execute_input": "2021-05-27T13:04:17.325330Z",
     "iopub.status.busy": "2021-05-27T13:04:17.325330Z",
     "iopub.status.idle": "2021-05-27T13:04:17.354142Z",
     "shell.execute_reply": "2021-05-27T13:04:17.354142Z",
     "shell.execute_reply.started": "2021-05-27T13:04:17.325330Z"
    },
    "jupyter": {
     "outputs_hidden": true,
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Flight1</th>\n",
       "      <th>Flight2</th>\n",
       "      <th>Flight3</th>\n",
       "      <th>Class</th>\n",
       "      <th>Fare</th>\n",
       "      <th>TRD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>B</td>\n",
       "      <td>230.00</td>\n",
       "      <td>2.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>G</td>\n",
       "      <td>100.00</td>\n",
       "      <td>2.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>H</td>\n",
       "      <td>105.00</td>\n",
       "      <td>0.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>K</td>\n",
       "      <td>94.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>L</td>\n",
       "      <td>145.00</td>\n",
       "      <td>13.12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Origin Destination       Flight1 Flight2 Flight3 Class   Fare   TRD\n",
       "0    BER         BOD  AA0040BERBOD       .       .     B 230.00  2.29\n",
       "1    BER         BOD  AA0040BERBOD       .       .     G 100.00  2.07\n",
       "2    BER         BOD  AA0040BERBOD       .       .     H 105.00  0.66\n",
       "3    BER         BOD  AA0040BERBOD       .       .     K  94.00  0.00\n",
       "4    BER         BOD  AA0040BERBOD       .       .     L 145.00 13.12"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "old_df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-27T13:07:43.412812Z",
     "iopub.status.busy": "2021-05-27T13:07:43.412812Z",
     "iopub.status.idle": "2021-05-27T13:07:43.428817Z",
     "shell.execute_reply": "2021-05-27T13:07:43.428817Z",
     "shell.execute_reply.started": "2021-05-27T13:07:43.412812Z"
    },
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "def calc2(df):\n",
    "    df_g = df.groupby(list(df.columns[:5]))[['TRD']].sum()\n",
    "    return df_g"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-27T13:07:47.494729Z",
     "iopub.status.busy": "2021-05-27T13:07:47.494729Z",
     "iopub.status.idle": "2021-05-27T13:07:47.541689Z",
     "shell.execute_reply": "2021-05-27T13:07:47.541689Z",
     "shell.execute_reply.started": "2021-05-27T13:07:47.494729Z"
    },
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "s = calc2(old_df1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": true,
    "execution": {
     "iopub.execute_input": "2021-05-27T13:09:00.570695Z",
     "iopub.status.busy": "2021-05-27T13:09:00.570695Z",
     "iopub.status.idle": "2021-05-27T13:09:00.615291Z",
     "shell.execute_reply": "2021-05-27T13:09:00.615291Z",
     "shell.execute_reply.started": "2021-05-27T13:09:00.570695Z"
    },
    "jupyter": {
     "outputs_hidden": true,
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Flight1</th>\n",
       "      <th>Flight2</th>\n",
       "      <th>Flight3</th>\n",
       "      <th>TRD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>798</th>\n",
       "      <td>PHH</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0858PHHTFB</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>263.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1174</th>\n",
       "      <td>TFB</td>\n",
       "      <td>PSE</td>\n",
       "      <td>AA0020TFBPSE</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>258.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>970</th>\n",
       "      <td>QWS</td>\n",
       "      <td>MBY</td>\n",
       "      <td>AA0259QWSMBY</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>253.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254</th>\n",
       "      <td>CMJ</td>\n",
       "      <td>MBT</td>\n",
       "      <td>AA0628CMJMBT</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>247.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1132</th>\n",
       "      <td>TFB</td>\n",
       "      <td>MBT</td>\n",
       "      <td>AA0608TFBMBT</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>244.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1419</th>\n",
       "      <td>ZBL</td>\n",
       "      <td>DEW</td>\n",
       "      <td>AA0061ZBLDEW</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182</th>\n",
       "      <td>BOD</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0062BODTFB</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1033</th>\n",
       "      <td>TFB</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0065TFBBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>431</th>\n",
       "      <td>GBJ</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0062GBJTFB</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>BOD</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0066BODTFB</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1430 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Origin Destination       Flight1 Flight2 Flight3    TRD\n",
       "798     PHH         TFB  AA0858PHHTFB       .       . 263.11\n",
       "1174    TFB         PSE  AA0020TFBPSE       .       . 258.42\n",
       "970     QWS         MBY  AA0259QWSMBY       .       . 253.43\n",
       "254     CMJ         MBT  AA0628CMJMBT       .       . 247.40\n",
       "1132    TFB         MBT  AA0608TFBMBT       .       . 244.10\n",
       "...     ...         ...           ...     ...     ...    ...\n",
       "1419    ZBL         DEW  AA0061ZBLDEW       .       .   0.00\n",
       "182     BOD         TFB  AA0062BODTFB       .       .   0.00\n",
       "1033    TFB         BOD  AA0065TFBBOD       .       .   0.00\n",
       "431     GBJ         TFB  AA0062GBJTFB       .       .   0.00\n",
       "184     BOD         TFB  AA0066BODTFB       .       .   0.00\n",
       "\n",
       "[1430 rows x 6 columns]"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = s.reset_index()\n",
    "s.sort_values(by='TRD',ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "collapsed": true,
    "execution": {
     "iopub.execute_input": "2021-05-27T13:10:04.827968Z",
     "iopub.status.busy": "2021-05-27T13:10:04.827968Z",
     "iopub.status.idle": "2021-05-27T13:10:04.860185Z",
     "shell.execute_reply": "2021-05-27T13:10:04.860185Z",
     "shell.execute_reply.started": "2021-05-27T13:10:04.827968Z"
    },
    "jupyter": {
     "outputs_hidden": true,
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>机型</th>\n",
       "      <th>座位数</th>\n",
       "      <th>飞机数量</th>\n",
       "      <th>每小时飞行成本</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>F8C12Y126</td>\n",
       "      <td>126</td>\n",
       "      <td>39</td>\n",
       "      <td>8350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>F12C0Y132</td>\n",
       "      <td>132</td>\n",
       "      <td>8</td>\n",
       "      <td>6281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>F8C12Y99</td>\n",
       "      <td>99</td>\n",
       "      <td>9</td>\n",
       "      <td>4597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>F12C12Y48</td>\n",
       "      <td>48</td>\n",
       "      <td>11</td>\n",
       "      <td>1908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>F0C0Y72</td>\n",
       "      <td>72</td>\n",
       "      <td>4</td>\n",
       "      <td>2390</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          机型  座位数  飞机数量  每小时飞行成本\n",
       "0  F8C12Y126  126    39     8350\n",
       "1  F12C0Y132  132     8     6281\n",
       "2   F8C12Y99   99     9     4597\n",
       "3  F12C12Y48   48    11     1908\n",
       "4    F0C0Y72   72     4     2390"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fleet_df = pd.read_csv(os.path.join(data_dir,data_files[1]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "collapsed": true,
    "execution": {
     "iopub.execute_input": "2021-05-27T13:10:13.680686Z",
     "iopub.status.busy": "2021-05-27T13:10:13.680686Z",
     "iopub.status.idle": "2021-05-27T13:10:13.724547Z",
     "shell.execute_reply": "2021-05-27T13:10:13.724547Z",
     "shell.execute_reply.started": "2021-05-27T13:10:13.680686Z"
    },
    "jupyter": {
     "outputs_hidden": true,
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>机型</th>\n",
       "      <th>座位数</th>\n",
       "      <th>飞机数量</th>\n",
       "      <th>每小时飞行成本</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>F8C12Y126</td>\n",
       "      <td>126</td>\n",
       "      <td>39</td>\n",
       "      <td>8350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>F12C0Y132</td>\n",
       "      <td>132</td>\n",
       "      <td>8</td>\n",
       "      <td>6281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>F8C12Y99</td>\n",
       "      <td>99</td>\n",
       "      <td>9</td>\n",
       "      <td>4597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>F12C12Y48</td>\n",
       "      <td>48</td>\n",
       "      <td>11</td>\n",
       "      <td>1908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>F0C0Y72</td>\n",
       "      <td>72</td>\n",
       "      <td>4</td>\n",
       "      <td>2390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>F0C0Y76</td>\n",
       "      <td>76</td>\n",
       "      <td>38</td>\n",
       "      <td>2390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>F12C0Y112</td>\n",
       "      <td>112</td>\n",
       "      <td>10</td>\n",
       "      <td>4540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>F12C30Y117</td>\n",
       "      <td>117</td>\n",
       "      <td>44</td>\n",
       "      <td>5159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>F16C0Y165</td>\n",
       "      <td>165</td>\n",
       "      <td>48</td>\n",
       "      <td>4602</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           机型  座位数  飞机数量  每小时飞行成本\n",
       "0   F8C12Y126  126    39     8350\n",
       "1   F12C0Y132  132     8     6281\n",
       "2    F8C12Y99   99     9     4597\n",
       "3   F12C12Y48   48    11     1908\n",
       "4     F0C0Y72   72     4     2390\n",
       "5     F0C0Y76   76    38     2390\n",
       "6   F12C0Y112  112    10     4540\n",
       "7  F12C30Y117  117    44     5159\n",
       "8   F16C0Y165  165    48     4602"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fleet_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T11:12:01.791150Z",
     "iopub.status.busy": "2021-05-28T11:12:01.790148Z",
     "iopub.status.idle": "2021-05-28T11:12:01.806191Z",
     "shell.execute_reply": "2021-05-28T11:12:01.804190Z",
     "shell.execute_reply.started": "2021-05-28T11:12:01.791150Z"
    }
   },
   "outputs": [],
   "source": [
    "df = new_df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T11:12:03.233358Z",
     "iopub.status.busy": "2021-05-28T11:12:03.231356Z",
     "iopub.status.idle": "2021-05-28T11:12:03.296360Z",
     "shell.execute_reply": "2021-05-28T11:12:03.295358Z",
     "shell.execute_reply.started": "2021-05-28T11:12:03.232357Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Flight1</th>\n",
       "      <th>Flight2</th>\n",
       "      <th>Flight3</th>\n",
       "      <th>Class</th>\n",
       "      <th>Fare</th>\n",
       "      <th>RD0</th>\n",
       "      <th>RD1</th>\n",
       "      <th>RD2</th>\n",
       "      <th>...</th>\n",
       "      <th>RD89</th>\n",
       "      <th>RD99</th>\n",
       "      <th>RD119</th>\n",
       "      <th>RD139</th>\n",
       "      <th>RD159</th>\n",
       "      <th>RD179</th>\n",
       "      <th>RD209</th>\n",
       "      <th>RD239</th>\n",
       "      <th>RD269</th>\n",
       "      <th>RD330</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>230.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>G</td>\n",
       "      <td>100.00</td>\n",
       "      <td>0.67</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>H</td>\n",
       "      <td>105.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>K</td>\n",
       "      <td>94.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>L</td>\n",
       "      <td>145.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.67</td>\n",
       "      <td>1.33</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 38 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Origin Destination       Flight1 Flight2 Flight3 Class   Fare  RD0  RD1  \\\n",
       "0    BER         BOD  AA0040BERBOD     NaN     NaN     B 230.00 0.00 0.00   \n",
       "1    BER         BOD  AA0040BERBOD     NaN     NaN     G 100.00 0.67 0.00   \n",
       "2    BER         BOD  AA0040BERBOD     NaN     NaN     H 105.00 0.00 0.00   \n",
       "3    BER         BOD  AA0040BERBOD     NaN     NaN     K  94.00 0.00 0.00   \n",
       "4    BER         BOD  AA0040BERBOD     NaN     NaN     L 145.00 0.00 0.00   \n",
       "\n",
       "   RD2  ...  RD89  RD99  RD119  RD139  RD159  RD179  RD209  RD239  RD269  \\\n",
       "0 0.00  ...  0.00  0.00   0.00   0.00   0.00   0.00   0.00   0.00   0.00   \n",
       "1 0.00  ...  0.00  0.00   0.00   0.00   0.00   0.00   0.00   0.00   0.00   \n",
       "2 0.00  ...  0.00  0.00   0.00   0.00   0.00   0.00   0.00   0.00   0.00   \n",
       "3 0.00  ...  0.00  0.00   0.00   0.00   0.00   0.00   0.00   0.00   0.00   \n",
       "4 0.00  ...  0.00  0.67   1.33   0.00   0.00   0.00   0.00   0.00   0.00   \n",
       "\n",
       "   RD330  \n",
       "0   0.00  \n",
       "1   0.00  \n",
       "2   0.00  \n",
       "3   0.00  \n",
       "4   0.00  \n",
       "\n",
       "[5 rows x 38 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.replace('.',np.nan)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T11:12:04.576091Z",
     "iopub.status.busy": "2021-05-28T11:12:04.576091Z",
     "iopub.status.idle": "2021-05-28T11:12:04.644130Z",
     "shell.execute_reply": "2021-05-28T11:12:04.642129Z",
     "shell.execute_reply.started": "2021-05-28T11:12:04.576091Z"
    }
   },
   "outputs": [],
   "source": [
    "df[\"TRD\"] = df.iloc[:,7:].sum(axis=1)\n",
    "df.drop(columns=df.columns[7:-1],inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T11:12:04.653132Z",
     "iopub.status.busy": "2021-05-28T11:12:04.652130Z",
     "iopub.status.idle": "2021-05-28T11:12:04.675137Z",
     "shell.execute_reply": "2021-05-28T11:12:04.674133Z",
     "shell.execute_reply.started": "2021-05-28T11:12:04.653132Z"
    }
   },
   "outputs": [],
   "source": [
    "flight = df.loc[:,df.columns[df.columns.str.match(\"Flight\")]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T11:12:05.854223Z",
     "iopub.status.busy": "2021-05-28T11:12:05.853223Z",
     "iopub.status.idle": "2021-05-28T11:12:05.885229Z",
     "shell.execute_reply": "2021-05-28T11:12:05.883225Z",
     "shell.execute_reply.started": "2021-05-28T11:12:05.853223Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        1\n",
       "1        1\n",
       "2        1\n",
       "3        1\n",
       "4        1\n",
       "        ..\n",
       "47185    1\n",
       "47186    1\n",
       "47187    1\n",
       "47188    1\n",
       "47189    1\n",
       "Length: 47190, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flight.notna().sum(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T11:12:07.127327Z",
     "iopub.status.busy": "2021-05-28T11:12:07.126328Z",
     "iopub.status.idle": "2021-05-28T11:12:07.175348Z",
     "shell.execute_reply": "2021-05-28T11:12:07.163347Z",
     "shell.execute_reply.started": "2021-05-28T11:12:07.127327Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Flight1</th>\n",
       "      <th>Flight2</th>\n",
       "      <th>Flight3</th>\n",
       "      <th>Class</th>\n",
       "      <th>Fare</th>\n",
       "      <th>TRD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>230.00</td>\n",
       "      <td>2.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>G</td>\n",
       "      <td>100.00</td>\n",
       "      <td>2.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>H</td>\n",
       "      <td>105.00</td>\n",
       "      <td>0.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>K</td>\n",
       "      <td>94.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>L</td>\n",
       "      <td>145.00</td>\n",
       "      <td>13.12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Origin Destination       Flight1 Flight2 Flight3 Class   Fare   TRD\n",
       "0    BER         BOD  AA0040BERBOD     NaN     NaN     B 230.00  2.29\n",
       "1    BER         BOD  AA0040BERBOD     NaN     NaN     G 100.00  2.07\n",
       "2    BER         BOD  AA0040BERBOD     NaN     NaN     H 105.00  0.66\n",
       "3    BER         BOD  AA0040BERBOD     NaN     NaN     K  94.00  0.00\n",
       "4    BER         BOD  AA0040BERBOD     NaN     NaN     L 145.00 13.12"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T11:12:08.368530Z",
     "iopub.status.busy": "2021-05-28T11:12:08.367527Z",
     "iopub.status.idle": "2021-05-28T11:12:08.397532Z",
     "shell.execute_reply": "2021-05-28T11:12:08.395534Z",
     "shell.execute_reply.started": "2021-05-28T11:12:08.368530Z"
    }
   },
   "outputs": [],
   "source": [
    "df[\"Transfer times\"] = flight.notna().sum(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T11:12:09.545619Z",
     "iopub.status.busy": "2021-05-28T11:12:09.544619Z",
     "iopub.status.idle": "2021-05-28T11:12:09.572622Z",
     "shell.execute_reply": "2021-05-28T11:12:09.571622Z",
     "shell.execute_reply.started": "2021-05-28T11:12:09.545619Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Flight1</th>\n",
       "      <th>Flight2</th>\n",
       "      <th>Flight3</th>\n",
       "      <th>Class</th>\n",
       "      <th>Fare</th>\n",
       "      <th>TRD</th>\n",
       "      <th>Transfer times</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>230.00</td>\n",
       "      <td>2.29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>G</td>\n",
       "      <td>100.00</td>\n",
       "      <td>2.07</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>H</td>\n",
       "      <td>105.00</td>\n",
       "      <td>0.66</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>K</td>\n",
       "      <td>94.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>L</td>\n",
       "      <td>145.00</td>\n",
       "      <td>13.12</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Origin Destination       Flight1 Flight2 Flight3 Class   Fare   TRD  \\\n",
       "0    BER         BOD  AA0040BERBOD     NaN     NaN     B 230.00  2.29   \n",
       "1    BER         BOD  AA0040BERBOD     NaN     NaN     G 100.00  2.07   \n",
       "2    BER         BOD  AA0040BERBOD     NaN     NaN     H 105.00  0.66   \n",
       "3    BER         BOD  AA0040BERBOD     NaN     NaN     K  94.00  0.00   \n",
       "4    BER         BOD  AA0040BERBOD     NaN     NaN     L 145.00 13.12   \n",
       "\n",
       "   Transfer times  \n",
       "0               1  \n",
       "1               1  \n",
       "2               1  \n",
       "3               1  \n",
       "4               1  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T11:12:09.579621Z",
     "iopub.status.busy": "2021-05-28T11:12:09.579621Z",
     "iopub.status.idle": "2021-05-28T11:12:09.587621Z",
     "shell.execute_reply": "2021-05-28T11:12:09.586623Z",
     "shell.execute_reply.started": "2021-05-28T11:12:09.579621Z"
    }
   },
   "outputs": [],
   "source": [
    "df_g = df.groupby(\"Transfer times\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T11:12:18.356332Z",
     "iopub.status.busy": "2021-05-28T11:12:18.355329Z",
     "iopub.status.idle": "2021-05-28T11:12:18.500339Z",
     "shell.execute_reply": "2021-05-28T11:12:18.498336Z",
     "shell.execute_reply.started": "2021-05-28T11:12:18.356332Z"
    }
   },
   "outputs": [],
   "source": [
    "groups = []\n",
    "for label,group in df_g:\n",
    "    group = group.replace('.',np.nan).dropna(axis=1,how='all')\n",
    "    flight_colums = group.loc[:,group.columns.str.match(\"Flight\")]\n",
    "    group.loc[:,group.columns.str.match(\"Flight\")] = flight_colums.applymap(lambda x:x[:6])\n",
    "    groups.append(group)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T11:12:19.438412Z",
     "iopub.status.busy": "2021-05-28T11:12:19.438412Z",
     "iopub.status.idle": "2021-05-28T11:12:19.465247Z",
     "shell.execute_reply": "2021-05-28T11:12:19.463231Z",
     "shell.execute_reply.started": "2021-05-28T11:12:19.438412Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Flight1</th>\n",
       "      <th>Class</th>\n",
       "      <th>Fare</th>\n",
       "      <th>TRD</th>\n",
       "      <th>Transfer times</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>B</td>\n",
       "      <td>230.00</td>\n",
       "      <td>2.29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>G</td>\n",
       "      <td>100.00</td>\n",
       "      <td>2.07</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>H</td>\n",
       "      <td>105.00</td>\n",
       "      <td>0.66</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>K</td>\n",
       "      <td>94.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>L</td>\n",
       "      <td>145.00</td>\n",
       "      <td>13.12</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Origin Destination Flight1 Class   Fare   TRD  Transfer times\n",
       "0    BER         BOD  AA0040     B 230.00  2.29               1\n",
       "1    BER         BOD  AA0040     G 100.00  2.07               1\n",
       "2    BER         BOD  AA0040     H 105.00  0.66               1\n",
       "3    BER         BOD  AA0040     K  94.00  0.00               1\n",
       "4    BER         BOD  AA0040     L 145.00 13.12               1"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups[0].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T11:12:20.729363Z",
     "iopub.status.busy": "2021-05-28T11:12:20.729363Z",
     "iopub.status.idle": "2021-05-28T11:12:20.766870Z",
     "shell.execute_reply": "2021-05-28T11:12:20.764871Z",
     "shell.execute_reply.started": "2021-05-28T11:12:20.729363Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "517       True\n",
       "518       True\n",
       "519       True\n",
       "520       True\n",
       "521       True\n",
       "         ...  \n",
       "46910    False\n",
       "46911    False\n",
       "46912    False\n",
       "46913    False\n",
       "46914    False\n",
       "Length: 2266, dtype: bool"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flight_colums.apply(lambda x:x.str.match(r\"^AA\")).any(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T11:15:44.595995Z",
     "iopub.status.busy": "2021-05-28T11:15:44.594996Z",
     "iopub.status.idle": "2021-05-28T11:15:44.633999Z",
     "shell.execute_reply": "2021-05-28T11:15:44.631996Z",
     "shell.execute_reply.started": "2021-05-28T11:15:44.595995Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Flight1</th>\n",
       "      <th>Class</th>\n",
       "      <th>Fare</th>\n",
       "      <th>TRD</th>\n",
       "      <th>Transfer times</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>B</td>\n",
       "      <td>230.00</td>\n",
       "      <td>2.29</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>G</td>\n",
       "      <td>100.00</td>\n",
       "      <td>2.07</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>H</td>\n",
       "      <td>105.00</td>\n",
       "      <td>0.66</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>K</td>\n",
       "      <td>94.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>L</td>\n",
       "      <td>145.00</td>\n",
       "      <td>13.12</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47185</th>\n",
       "      <td>IOM</td>\n",
       "      <td>CMJ</td>\n",
       "      <td>AA2702</td>\n",
       "      <td>Q</td>\n",
       "      <td>211.55</td>\n",
       "      <td>8.67</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47186</th>\n",
       "      <td>IOM</td>\n",
       "      <td>CMJ</td>\n",
       "      <td>AA2702</td>\n",
       "      <td>S</td>\n",
       "      <td>133.41</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47187</th>\n",
       "      <td>IOM</td>\n",
       "      <td>CMJ</td>\n",
       "      <td>AA2702</td>\n",
       "      <td>T</td>\n",
       "      <td>312.68</td>\n",
       "      <td>1.43</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47188</th>\n",
       "      <td>IOM</td>\n",
       "      <td>CMJ</td>\n",
       "      <td>AA2702</td>\n",
       "      <td>V</td>\n",
       "      <td>380.14</td>\n",
       "      <td>14.60</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47189</th>\n",
       "      <td>IOM</td>\n",
       "      <td>CMJ</td>\n",
       "      <td>AA2702</td>\n",
       "      <td>Y</td>\n",
       "      <td>490.20</td>\n",
       "      <td>27.30</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10142 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Origin Destination Flight1 Class   Fare   TRD  Transfer times\n",
       "0        BER         BOD  AA0040     B 230.00  2.29               1\n",
       "1        BER         BOD  AA0040     G 100.00  2.07               1\n",
       "2        BER         BOD  AA0040     H 105.00  0.66               1\n",
       "3        BER         BOD  AA0040     K  94.00  0.00               1\n",
       "4        BER         BOD  AA0040     L 145.00 13.12               1\n",
       "...      ...         ...     ...   ...    ...   ...             ...\n",
       "47185    IOM         CMJ  AA2702     Q 211.55  8.67               1\n",
       "47186    IOM         CMJ  AA2702     S 133.41  0.00               1\n",
       "47187    IOM         CMJ  AA2702     T 312.68  1.43               1\n",
       "47188    IOM         CMJ  AA2702     V 380.14 14.60               1\n",
       "47189    IOM         CMJ  AA2702     Y 490.20 27.30               1\n",
       "\n",
       "[10142 rows x 7 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T12:29:27.990330Z",
     "iopub.status.busy": "2021-05-28T12:29:27.990330Z",
     "iopub.status.idle": "2021-05-28T12:29:28.023834Z",
     "shell.execute_reply": "2021-05-28T12:29:28.022820Z",
     "shell.execute_reply.started": "2021-05-28T12:29:27.990330Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Flight1</th>\n",
       "      <th>Flight2</th>\n",
       "      <th>Class</th>\n",
       "      <th>Fare</th>\n",
       "      <th>TRD</th>\n",
       "      <th>Transfer times</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>BER</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>AA0104</td>\n",
       "      <td>B</td>\n",
       "      <td>512.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>BER</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>AA0104</td>\n",
       "      <td>G</td>\n",
       "      <td>252.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>BER</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>AA0104</td>\n",
       "      <td>H</td>\n",
       "      <td>512.00</td>\n",
       "      <td>0.33</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>BER</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>AA0104</td>\n",
       "      <td>K</td>\n",
       "      <td>245.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>BER</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0040</td>\n",
       "      <td>AA0104</td>\n",
       "      <td>L</td>\n",
       "      <td>341.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Origin Destination Flight1 Flight2 Class   Fare  TRD  Transfer times\n",
       "22    BER         TFB  AA0040  AA0104     B 512.00 0.00               2\n",
       "23    BER         TFB  AA0040  AA0104     G 252.00 0.00               2\n",
       "24    BER         TFB  AA0040  AA0104     H 512.00 0.33               2\n",
       "25    BER         TFB  AA0040  AA0104     K 245.00 0.00               2\n",
       "26    BER         TFB  AA0040  AA0104     L 341.00 0.00               2"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups[1].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T12:30:30.524247Z",
     "iopub.status.busy": "2021-05-28T12:30:30.524247Z",
     "iopub.status.idle": "2021-05-28T12:30:30.547248Z",
     "shell.execute_reply": "2021-05-28T12:30:30.546249Z",
     "shell.execute_reply.started": "2021-05-28T12:30:30.524247Z"
    }
   },
   "outputs": [],
   "source": [
    "schedual = pd.read_csv(os.path.join(data_dir,data_files[4]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-28T12:30:35.329871Z",
     "iopub.status.busy": "2021-05-28T12:30:35.329871Z",
     "iopub.status.idle": "2021-05-28T12:30:35.356873Z",
     "shell.execute_reply": "2021-05-28T12:30:35.353892Z",
     "shell.execute_reply.started": "2021-05-28T12:30:35.329871Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>flight</th>\n",
       "      <th>deptime</th>\n",
       "      <th>depoff</th>\n",
       "      <th>arrtime</th>\n",
       "      <th>arroff</th>\n",
       "      <th>origin</th>\n",
       "      <th>destination</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AA0001</td>\n",
       "      <td>755</td>\n",
       "      <td>-400</td>\n",
       "      <td>1038</td>\n",
       "      <td>-700</td>\n",
       "      <td>EDB</td>\n",
       "      <td>TFB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AA0002</td>\n",
       "      <td>1405</td>\n",
       "      <td>-700</td>\n",
       "      <td>2159</td>\n",
       "      <td>-400</td>\n",
       "      <td>TFB</td>\n",
       "      <td>EDB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AA0003</td>\n",
       "      <td>1740</td>\n",
       "      <td>-400</td>\n",
       "      <td>2025</td>\n",
       "      <td>-700</td>\n",
       "      <td>EDB</td>\n",
       "      <td>TFB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AA0004</td>\n",
       "      <td>840</td>\n",
       "      <td>-700</td>\n",
       "      <td>1640</td>\n",
       "      <td>-400</td>\n",
       "      <td>TFB</td>\n",
       "      <td>EDB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AA0005</td>\n",
       "      <td>910</td>\n",
       "      <td>-400</td>\n",
       "      <td>1202</td>\n",
       "      <td>-700</td>\n",
       "      <td>EDB</td>\n",
       "      <td>MBY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   flight  deptime  depoff  arrtime  arroff origin destination\n",
       "0  AA0001      755    -400     1038    -700    EDB         TFB\n",
       "1  AA0002     1405    -700     2159    -400    TFB         EDB\n",
       "2  AA0003     1740    -400     2025    -700    EDB         TFB\n",
       "3  AA0004      840    -700     1640    -400    TFB         EDB\n",
       "4  AA0005      910    -400     1202    -700    EDB         MBY"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "schedual.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
